﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using Antu.DataAccess;

namespace Antu.Business
{
    public class SetPersonsFlag
    {
        private string _connString = "";
        public string ConnString
        {
            get
            {
                return _connString;
            }
            set
            {
                _connString = value;
            }
        }
        public SetPersonsFlag(string conn)
        {
            ConnString = conn;
        }
        public void SetFlag(int i)
        {
            if (i == 1)//公有住房
            {
                
                string strSql = string.Format(@"select a.*,
                                        iif(a.身份证='' or a.身份证 is null,a.军官证,a.身份证) as 证件号码 
                                        from  用户信息表 a where a.flag=0 and a.购房人姓名 is not null");
                DataTable dtTemp;
                SysParams.ConnectionString = ConnString;
                SysParams.OAConnection(false, DatabaseType.Access).RunSql(strSql, out dtTemp);

                strSql = string.Format("select distinct 单位编号 from dj_para where 单位编号 is not null");
                string unitNo = string.Empty;
                SysParams.ConnectionString = ConnString;
                unitNo=SysParams.OAConnection(false, DatabaseType.Access).GetValue(strSql);
                foreach (DataRow row in dtTemp.Rows)
                {
                    string aiid = row["aiid"].ToString();
                    string buyername = row["购房人姓名"].ToString();
                    string buyercard = row["证件号码"].ToString();
                    string unitno = unitNo;

                    strSql = string.Format(@"select a.* from zf_housebuy a
                    where unitno='{0}' and aid='{1}' and buyername='{2}' and buyercardno='{3}' and a.iid in(select iid from st_instance where status<>-2)", 
                    unitno, aiid, buyername, buyercard);
                    DataTable dtBuyers;
                    SysParams.OAConnection(false, DatabaseType.Oracle).RunSql(strSql, out dtBuyers);
                    if (dtBuyers.Rows.Count > 0)
                    {
                        strSql = string.Format("update 用户信息表 set flag=3 where aiid={0}", aiid);
                        SysParams.ConnectionString = ConnString;
                        SysParams.OAConnection(false, DatabaseType.Access).RunSql(strSql);
                    }
                }
            }
            else//住房补贴
            {
                string strSql = string.Format(@"select *,iif(身份证号码 is null or 身份证号码='',军官证号码,身份证号码) as  证件号码
                from 单位职工购房工龄补贴清册 where 姓名 is not null and flag=0");
                DataTable dtTemp;
                SysParams.ConnectionString = ConnString;
                SysParams.OAConnection(false, DatabaseType.Access).RunSql(strSql, out dtTemp);
                strSql = string.Format("select distinct 单位编号 from dj_para where 单位编号 is not null");
                string unitNo = string.Empty;
                SysParams.ConnectionString = ConnString;
                unitNo = SysParams.OAConnection(false, DatabaseType.Access).GetValue(strSql);
                foreach (DataRow row in dtTemp.Rows)
                {
                    string aiid = row["aiid"].ToString();
                    string buyername = row["姓名"].ToString();
                    string buyercard = row["证件号码"].ToString();
                    string unitno = unitNo;
                    strSql = string.Format(@"select a.* from UT_购房工龄补贴申请附表二 a where 申请人姓名='{0}' and aiid='{1}' 
                    and 申请人身份证号码='{2}' and a.iid in(select iid from ut_职工购房工龄补贴附表三 where 单位编号='{3}')",
                    buyername,aiid,buyercard,unitno);
                    DataTable dtBuyers;
                    SysParams.OAConnection(false, DatabaseType.Oracle).RunSql(strSql,out dtBuyers);
                    if (dtBuyers.Rows.Count > 0)
                    {
                        strSql = string.Format("update 单位职工购房工龄补贴清册 set flag=3 where aiid={0}",aiid);
                        SysParams.ConnectionString = ConnString;
                        SysParams.OAConnection(false, DatabaseType.Access).RunSql(strSql);
                    }
                }
            }
        }
    }
}
